VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "classExcelBatch"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'+
' VBA module modTestCase.  Excel AddIn TestCase.xla
' Copy revenue matrices to transaction file (TransIN).
' For each open workbook, for each sheet, match the test case and proc
' See sub initBuild for custom settings.
'
' 2003-10-22, JFW, FXRS TD Jan04.
' 2003-10-23, JFW, FXRS Bundling Jan04.
'
' 2004-10-20, JFW, Fix WAPI dims, were duplicated, had zeros.
' 2004-10-20, JFW, WAPI code had wrong column for SCO.
'~

Option Explicit

Dim m_objExcel As Excel.Application
Dim m_objWorkbook As Workbook
Dim m_colRecords As Collection          ' All batch records
Dim m_colRecord As Collection           ' The current batch record
Dim lngCount As Long
Dim lngCountError As Long
Dim m_lngPackageCount As Long
Dim m_boolIncludeTD As Boolean

Dim m_colHeadings As Collection
'


Public Function Count() As Long
    Count = lngCount
End Function

Public Function CountError() As Long
    CountError = lngCountError
End Function

Public Function CountTotal() As Long
    CountTotal = m_colRecords.Count
End Function

Public Function ExcelImport(ExcelFileName As String)

    On Error GoTo ErrorExit
    
    Dim objSheet As Worksheet
    
    Dim strAddress As String
    
    Set m_colRecords = New Collection
    
'    If Window.Find("EXCEL.EXE") <> 0 Then
'        MsgBox "Excel is running.  Please close before running a batch.", vbApplicationModal + vbSystemModal + vbExclamation
'        Exit Function
'    End If
    
    Set m_objExcel = New Excel.Application
    Set m_objWorkbook = m_objExcel.Workbooks.Open(ExcelFileName, , False, , , , True)
    
    If m_objWorkbook.ReadOnly Then
        Err.Raise 12345, "ExcelImport", "Workbook " & ExcelFileName & " is open by another user."
    End If
    
    For Each objSheet In m_objWorkbook.Sheets
        
        Call SheetImport(objSheet)
        
    Next objSheet
    
    Exit Function

ErrorExit:
    Err.Raise 12345, "ExcelBatch.ExcelImport", Err.Description

End Function
'


Public Property Get PackageCount() As Long

    PackageCount = m_lngPackageCount

End Property
'

Public Property Let PackageCount(ByVal vNewValue As Long)

    If vNewValue < 1 Then
        m_lngPackageCount = 1
    Else
        m_lngPackageCount = vNewValue
    End If
    
End Property
'



Public Property Get IncludeTD() As Boolean

    IncludeTD = m_boolIncludeTD

End Property
'

Public Property Let IncludeTD(ByVal vNewValue As Boolean)

    m_boolIncludeTD = vNewValue
    
End Property
'


Public Sub SheetImport(aSheet As Worksheet)

    On Error GoTo ErrorExit
    
    Dim lngRow As Long
    Dim lngColumn As Long
    Dim strAddress As String
    
    Dim colRecord As Collection
    
    lngColumn = 1
    Set m_colHeadings = New Collection
    ' Loop until 2 consecutive column headings are blank
    Do While aSheet.Cells(1, lngColumn).Value <> "" Or aSheet.Cells(1, lngColumn + 1).Value <> ""
    
        Select Case True
        Case 0 = InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "CUST") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "ID")
            If IsNull(CollectionItem(m_colHeadings, "RecipientCode")) Then
                m_colHeadings.Add lngColumn, "RecipientCode"
            End If
        Case 0 = InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "COMP") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "NAME")
            If IsNull(CollectionItem(m_colHeadings, "RecipientCompany")) Then
                m_colHeadings.Add lngColumn, "RecipientCompany"
            End If
        Case 0 = InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "CONTACT")
            If IsNull(CollectionItem(m_colHeadings, "RecipientName")) Then
                m_colHeadings.Add lngColumn, "RecipientName"
            End If
        Case 0 = InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "PHONE")
            If IsNull(CollectionItem(m_colHeadings, "RecipientPhone")) Then
                m_colHeadings.Add lngColumn, "RecipientPhone"
            End If
        Case 0 = InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "ADDR") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "1")
            If IsNull(CollectionItem(m_colHeadings, "RecipientAddress1")) Then
                m_colHeadings.Add lngColumn, "RecipientAddress1"
            End If
        Case 0 = InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "ADDR") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "2")
            If IsNull(CollectionItem(m_colHeadings, "RecipientAddress2")) Then
                m_colHeadings.Add lngColumn, "RecipientAddress2"
            End If
        Case 0 = InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "POST") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "CODE")
            If IsNull(CollectionItem(m_colHeadings, "RecipientPostalCode")) Then
                m_colHeadings.Add lngColumn, "RecipientPostalCode"
            End If
        Case 0 = InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "COUNTRY")
            If IsNull(CollectionItem(m_colHeadings, "RecipientCountry")) Then
                m_colHeadings.Add lngColumn, "RecipientCountry"
            End If
        Case 0 = InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "CITY")
            If IsNull(CollectionItem(m_colHeadings, "RecipientCity")) Then
                m_colHeadings.Add lngColumn, "RecipientCity"
            End If
        Case 0 = InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "PROV")
            If IsNull(CollectionItem(m_colHeadings, "RecipientProvince")) Then
                m_colHeadings.Add lngColumn, "RecipientProvince"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "TRACKING")
            If IsNull(CollectionItem(m_colHeadings, "TrackingNumber")) Then
                m_colHeadings.Add lngColumn, "TrackingNumber"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "FREIGHT")
            If IsNull(CollectionItem(m_colHeadings, "FreightCharge")) Then
                m_colHeadings.Add lngColumn, "FreightCharge"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "BILL") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "OPT")
            If IsNull(CollectionItem(m_colHeadings, "PaymentType")) Then
                m_colHeadings.Add lngColumn, "PaymentType"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "PACK") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "COUNT")
            If IsNull(CollectionItem(m_colHeadings, "PackageCount")) Then
                m_colHeadings.Add lngColumn, "PackageCount"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "WEIGHT")
            If IsNull(CollectionItem(m_colHeadings, "Weight")) Then
                m_colHeadings.Add lngColumn, "Weight"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "DIMS")
            If IsNull(CollectionItem(m_colHeadings, "Dims")) Then
                m_colHeadings.Add lngColumn, "Dims"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "ACCTNO")
            If IsNull(CollectionItem(m_colHeadings, "OriginAccount")) Then
                m_colHeadings.Add lngColumn, "OriginAccount"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "NAME")
            If IsNull(CollectionItem(m_colHeadings, "OriginName")) Then
                m_colHeadings.Add lngColumn, "OriginName"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "ADDR1")
            If IsNull(CollectionItem(m_colHeadings, "OriginAddress1")) Then
                m_colHeadings.Add lngColumn, "OriginAddress1"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "ADDR2")
            If IsNull(CollectionItem(m_colHeadings, "OriginAddress2")) Then
                m_colHeadings.Add lngColumn, "OriginAddress2"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "POSTAL")
            If IsNull(CollectionItem(m_colHeadings, "OriginPostalCode")) Then
                m_colHeadings.Add lngColumn, "OriginPostalCode"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "CITY")
            If IsNull(CollectionItem(m_colHeadings, "OriginCity")) Then
                m_colHeadings.Add lngColumn, "OriginCity"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "PROV")
            If IsNull(CollectionItem(m_colHeadings, "OriginProvince")) Then
                m_colHeadings.Add lngColumn, "OriginProvince"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "SO") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "COUNTRY")
            If IsNull(CollectionItem(m_colHeadings, "OriginCountry")) Then
                m_colHeadings.Add lngColumn, "OriginCountry"
            End If
        Case 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "ST") _
        And 0 <> InStr(1, UCase(aSheet.Cells(1, lngColumn).Value), "TAXID")
            If IsNull(CollectionItem(m_colHeadings, "OriginTaxID")) Then
                m_colHeadings.Add lngColumn, "OriginTaxID"
            End If
        End Select
        
        lngColumn = lngColumn + 1
    Loop
    
    If m_colHeadings.Count < 5 Then
        Exit Sub
    End If
    
    If IsNull(CollectionItem(m_colHeadings, "TrackingNumber")) Then
        Err.Raise 12345, "SheetImport.Headings", "Spreadsheet requires 'TRACKING' column."
    End If
    
    lngRow = 2
    Do While aSheet.Cells(lngRow, 1).Value <> ""
        
        If aSheet.Cells(lngRow, m_colHeadings("TrackingNumber")) = "" Then
        
            Set colRecord = New Collection
            colRecord.Add aSheet, "Worksheet"
            colRecord.Add lngRow, "Row"
            colRecord.Add CollectionItem(m_colHeadings, "TrackingNumber"), "TrackingNumber"
            colRecord.Add CollectionItem(m_colHeadings, "FreightCharge"), "FreightCharge"
        
            On Error Resume Next
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("RecipientCode"))), "RecipientCode"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("RecipientCompany"))), "RecipientCompany"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("RecipientName"))), "RecipientName"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("RecipientPhone"))), "RecipientPhone"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("RecipientAddress1"))), "RecipientAddress1"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("RecipientAddress2"))), "RecipientAddress2"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("RecipientPostalCode"))), "RecipientPostalCode"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("RecipientCountry"))), "RecipientCountry"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("RecipientCity"))), "RecipientCity"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("RecipientProvince"))), "RecipientProvince"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("PaymentType"))), "PaymentType"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("PackageCount"))), "PackageCount"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("Weight"))), "Weight"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("Dims"))), "Dims"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("OriginAccount"))), "OriginAccount"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("OriginName"))), "OriginName"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("OriginAddress1"))), "OriginAddress1"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("OriginAddress2"))), "OriginAddress2"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("OriginPostalCode"))), "OriginPostalCode"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("OriginCity"))), "OriginCity"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("OriginProvince"))), "OriginProvince"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("OriginCountry"))), "OriginCountry"
            colRecord.Add Trim(aSheet.Cells(lngRow, m_colHeadings("OriginTaxID"))), "OriginTaxID"
            On Error GoTo ErrorExit
        
            m_colRecords.Add colRecord
            
        End If
        
        lngRow = lngRow + 1
    Loop

    Exit Sub

ErrorExit:
    Err.Raise 12345, "ExcelBatch.SheetImport", Err.Description
    If False Then
        Resume
    End If
End Sub



Public Sub AfterShip(TrackingNumber As String, FreightCharge As String)

    On Error GoTo ErrorExit
    
    ' Logfile "ExcelBatch.AfterShip"
    
    Dim aSheet As Worksheet
    Set aSheet = m_colRecord.Item("Worksheet")
    If Not IsNull(CollectionItem(m_colRecord, "TrackingNumber")) Then
            
            ' Logfile "ExcelBatch.AfterShip, tracking, before (" & aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("TrackingNumber")).Value & ")"
        
        aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("TrackingNumber")).ClearFormats
        aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("TrackingNumber")).Value = _
            IIf(aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("TrackingNumber")).Value = "", "'", "") _
            & aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("TrackingNumber")).Value _
            & IIf(aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("TrackingNumber")).Value <> "", ", ", "") _
            & TrackingNumber
    
            ' Logfile "ExcelBatch.AfterShip, tracking, after (" & aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("TrackingNumber")).Value & ")"
    
    End If
    If Not IsNull(CollectionItem(m_colRecord, "FreightCharge")) _
    And IsNumeric(FreightCharge) Then
        
            ' Logfile "ExcelBatch.AfterShip, charge, before (" & aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("FreightCharge")).Value & ")"
        
        aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("FreightCharge")).Value = _
            Val(aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("FreightCharge")).Value) _
            + Val(FreightCharge)
    
            ' Logfile "ExcelBatch.AfterShip, charge, after (" & aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("FreightCharge")).Value & ")"
    
    End If
    m_objWorkbook.Save
    Exit Sub
    
ErrorExit:
    Err.Raise 12345, "ExcelBatch.AfterShip", Err.Description
    
End Sub


Public Sub AfterShipError(ErrorMessage As String)

    On Error GoTo ErrorExit
        
    Dim aSheet As Worksheet
    Set aSheet = m_colRecord.Item("Worksheet")
    aSheet.Cells(m_colRecord.Item("Row"), m_colRecord.Item("TrackingNumber")).Value = ErrorMessage
    m_objWorkbook.Save
    
    lngCountError = lngCountError + 1
    
    Exit Sub
    
ErrorExit:
    Err.Raise 12345, "ExcelBatch.AfterShip", Err.Description
End Sub


Public Function ShipNext(CafeShipment As Shipment) As Boolean

    On Error GoTo ErrorExit
    
    Dim i As Long
    Dim objIntl As Object
    Dim objMPSTest As MPS
    Dim objIntlTest As Intl
    Dim objPackageTest As Package
    Dim objPackagempstest As MPSPackage
    Dim objMPSPackage As MPSPackage
    Dim objPackage As Object
    Dim objPackage1 As Object       ' For PO num of MPS packages
    Dim objSender As Sender
    Dim strPhone As String
    Dim objCommodity As Commodity
    
    lngCount = lngCount + 1
    
    If lngCount > m_colRecords.Count Then
        ShipNext = False
        Exit Function
    End If
    
    Set m_colRecord = m_colRecords(lngCount)
    
    Me.PackageCount = Val(StringNullToBlank(CollectionItem(m_colRecord, "PackageCount")))
    
    Me.IncludeTD = False
    If Not g_formArrayBatch Is Nothing Then
        If g_formArrayBatch.checkTD _
        And Trim(CollectionItem(m_colRecord, "RecipientCountry")) = "US" _
        And Trim(CollectionItem(m_colRecord, "RecipientProvince")) <> "PR" Then
            Me.IncludeTD = True
        End If
    End If
    
    ' Logfile "ShipNext, count to be " & Me.PackageCount & ", was " & CafeShipment.MPS.Packages.Count
    
    If Not IncludeTD And Me.PackageCount > 1 Then
        
        ' Change single piece to MPS
        If CafeShipment.MPS.Packages.Count <= 1 Then
            CafeShipment.MPS.ServiceType = CafeShipment.Package.ServiceType
            CafeShipment.MPS.PackageType = CafeShipment.Package.PackageType
            CafeShipment.MPS.International.PackageType = fsmFedEx_Intl_Box
            CafeShipment.MPS.PaymentType = CafeShipment.Package.PaymentType
            CafeShipment.MPS.AccountNo = CafeShipment.Package.AccountNo
            
            ' 2011-11-30, JFW, Move commodities from single to MPS
            CafeShipment.MPS.International.Commodities.Clear
            For Each objCommodity In CafeShipment.Package.Intl.Commodities
                With CafeShipment.MPS.International.Commodities.Add(CStr(CafeShipment.MPS.International.Commodities.Count + 1))
                    .Code = objCommodity.Code
                    .CountryOfMfg = objCommodity.CountryOfMfg
                    .Description = objCommodity.Description
                    .HarmonizedCode = objCommodity.HarmonizedCode
                    .MarksNumbers = objCommodity.MarksNumbers
                    .PartNumber = objCommodity.PartNumber
                    .SKU_Item_UPC = objCommodity.SKU_Item_UPC
                    .UnitOfMeasure = objCommodity.UnitOfMeasure
                    .UnitQuantity = objCommodity.UnitQuantity
                    .UnitValue = objCommodity.UnitValue
                    .Weight = objCommodity.Weight
                End With
            Next
            
        End If
        
        ' non-TD MPS, build packages using weight from prior shipment
        For i = 0 To Me.PackageCount - 1
            If (i + 1) > CafeShipment.MPS.Packages.Count Then
                ' Logfile "ShipNext, add package " & i
                CafeShipment.MPS.Packages.Add
            End If
            If CafeShipment.MPS.Packages(i).Weight > 1 Then
                ' Do nothing, we have weight
                ' Logfile "ShipNext, package " & i & ", weight OK " & CafeShipment.MPS.Packages(i).Weight
            ElseIf CafeShipment.Package.Weight > 1 Then
                ' 2011-11-30, JFW, Carry weight from single to MPS
                ' Logfile "ShipNext, package " & i & ", weight from 1 pc " & CafeShipment.Package.Weight
                CafeShipment.MPS.Packages(i).Weight = CafeShipment.Package.Weight
                ' 2011-11-30, JFW, Carry weight from single to MPS
                If CafeShipment.Package.Dimensions.Length > 0 Then
                    CafeShipment.MPS.Packages(i).Length = CafeShipment.Package.Dimensions.Length
                    CafeShipment.MPS.Packages(i).Width = CafeShipment.Package.Dimensions.Width
                    CafeShipment.MPS.Packages(i).Height = CafeShipment.Package.Dimensions.Height
                End If
                
            ElseIf i >= 1 Then
                ' Logfile "ShipNext, package " & i & ", weight from prior pc " & CafeShipment.MPS.Packages(i - 1).Weight
                CafeShipment.MPS.Packages(i).Weight = CafeShipment.MPS.Packages(i - 1).Weight
            Else
                ' Logfile "ShipNext, package " & i & ", weight " & 1
                CafeShipment.MPS.Packages(i).Weight = 1
            End If
        Next i
        Do
            If Me.PackageCount <= CafeShipment.MPS.Packages.Count Then Exit Do
            ' Logfile "ShipNext, remove package, count " & CafeShipment.MPS.Packages.Count
            CafeShipment.MPS.Packages.Remove CafeShipment.MPS.Packages.Count - 1
        Loop
    
    ' TD or single piece
    ElseIf (IncludeTD Or Me.PackageCount <= 1) _
        And CafeShipment.MPS.Packages.Count > 1 Then
            ' Change MPS to single piece
            CafeShipment.Package.Weight = CafeShipment.MPS.Packages(0).Weight
            CafeShipment.MPS.Packages.Clear
            CafeShipment.Package.ServiceType = CafeShipment.MPS.ServiceType
            CafeShipment.Package.PackageType = CafeShipment.MPS.PackageType
            CafeShipment.Package.Intl.PackageType = fsmFedEx_Intl_Box
            CafeShipment.Package.PaymentType = CafeShipment.MPS.PaymentType
            CafeShipment.Package.AccountNo = CafeShipment.MPS.AccountNo
    
            ' 2011-11-30, JFW, Carry weight from single to MPS
            For Each objMPSPackage In CafeShipment.MPS.Packages
                CafeShipment.Package.Dimensions.Length = objMPSPackage.Length
                CafeShipment.Package.Dimensions.Width = objMPSPackage.Width
                CafeShipment.Package.Dimensions.Height = objMPSPackage.Height
                Exit For
            Next objMPSPackage

            ' Logfile "ShipNext, 3, dims length " & CafeShipment.Package.Dimensions.Length
                
            ' 2011-11-30, JFW, Move commodities from single to MPS
            CafeShipment.Package.Intl.Commodities.Clear
            For Each objCommodity In CafeShipment.MPS.International.Commodities
                With CafeShipment.Package.Intl.Commodities.Add(CStr(CafeShipment.Package.Intl.Commodities.Count + 1))
                    .Code = objCommodity.Code
                    .CountryOfMfg = objCommodity.CountryOfMfg
                    .Description = objCommodity.Description
                    .HarmonizedCode = objCommodity.HarmonizedCode
                    .MarksNumbers = objCommodity.MarksNumbers
                    .PartNumber = objCommodity.PartNumber
                    .SKU_Item_UPC = objCommodity.SKU_Item_UPC
                    .UnitOfMeasure = objCommodity.UnitOfMeasure
                    .UnitQuantity = objCommodity.UnitQuantity
                    .UnitValue = objCommodity.UnitValue
                    .Weight = objCommodity.Weight
                End With
            Next
    
    ElseIf CafeShipment.Package.Weight < 1 Then
        CafeShipment.Package.Weight = 1
        
    End If
    
    If CafeShipment.MPS.Packages.Count > 1 Then
        ' Logfile "ShipNext, working on MPS"
        Set objPackage = CafeShipment.MPS
        Set objIntl = objPackage.International
    Else
        ' Logfile "ShipNext, working on Package"
        Set objPackage = CafeShipment.Package
        Set objIntl = objPackage.Intl
    End If
    
    objPackage.Recipient.Code = Trim(StringNullToBlank(CollectionItem(m_colRecord, "RecipientCode")))
    objPackage.Recipient.Company = Trim(StringNullToBlank(CollectionItem(m_colRecord, "RecipientCompany")))
    objPackage.Recipient.Contact = Trim(StringNullToBlank(CollectionItem(m_colRecord, "RecipientName")))
    objPackage.Recipient.Address1 = Trim(StringNullToBlank(CollectionItem(m_colRecord, "RecipientAddress1")))
    objPackage.Recipient.Address2 = Trim(StringNullToBlank(CollectionItem(m_colRecord, "RecipientAddress2")))
    objPackage.Recipient.City = Trim(CollectionItem(m_colRecord, "RecipientCity"))
    objPackage.Recipient.Country = Trim(CollectionItem(m_colRecord, "RecipientCountry"))
    objPackage.Recipient.State = Trim(CollectionItem(m_colRecord, "RecipientProvince"))

    If objPackage.Recipient.Country = "US" Then
        If Len(Trim(CollectionItem(m_colRecord, "RecipientPostalCode"))) <= 5 Then
            objPackage.Recipient.Zip = Right("00000" & Trim(CollectionItem(m_colRecord, "RecipientPostalCode")), 5)
        Else
            objPackage.Recipient.Zip = Right("000000000" & Trim(CollectionItem(m_colRecord, "RecipientPostalCode")), 9)
        End If
    Else
            objPackage.Recipient.Zip = CollectionItem(m_colRecord, "RecipientPostalCode")
    End If

    objPackage.Recipient.Phone = PhoneNumber(StringNullToBlank(CollectionItem(m_colRecord, "RecipientPhone")))
    ' Debug.Print "Phone 1 " & objPackage.Recipient.Phone
    If Len(objPackage.Recipient.Phone) <> 10 Then
        ' Show status window
        With StatusWindow
            .Alignment = fsiAlignCenter
            .BackColor = vbWhite
            .TopMost = fsiTopMost
            .Text = "Invalid phone number (" & StringNullToBlank(CollectionItem(m_colRecord, "RecipientPhone")) & ") for record " & StringNullToBlank(CollectionItem(m_colRecord, "RecipientCode"))
            .Show
        End With
        StatusWindow.Hide
        
        objPackage.Recipient.Phone = Cafe2.Screen.GetSender.Phone
    End If
    ' Debug.Print "Phone 2 " & objPackage.Recipient.Phone
    
    Select Case True
    Case 0 <> InStr(1, UCase(StringNullToBlank(CollectionItem(m_colRecord, "PaymentType"))), "COLLECT")
        objPackage.PaymentType = fsm_Collect
        objIntl.DutyTaxPayType = fsmBill_Sender
    Case 0 <> InStr(1, UCase(StringNullToBlank(CollectionItem(m_colRecord, "PaymentType"))), "PREPAID")
        objPackage.PaymentType = fsmBill_Sender
        objIntl.DutyTaxPayType = fsmBill_Sender
    Case 0 <> InStr(1, UCase(StringNullToBlank(CollectionItem(m_colRecord, "PaymentType"))), "RECIP")
        objPackage.PaymentType = fsmBill_Recipient
        objPackage.Recipient.AccountNo = StringNullToBlank(CollectionItem(m_colRecord, "OriginAccount"))
        objIntl.DutyTaxPayType = fsmBill_Recipient
        objIntl.DutyTaxAcctNo = StringNullToBlank(CollectionItem(m_colRecord, "OriginAccount"))
    Case 0 <> InStr(1, UCase(StringNullToBlank(CollectionItem(m_colRecord, "PaymentType"))), "THIRD")
        objPackage.PaymentType = fsmBill_3rd_Party
        objPackage.Recipient.AccountNo = StringNullToBlank(CollectionItem(m_colRecord, "OriginAccount"))
        objIntl.DutyTaxPayType = fsmBill_3rd_Party
        objIntl.DutyTaxAcctNo = StringNullToBlank(CollectionItem(m_colRecord, "OriginAccount"))
    Case 0 <> InStr(1, UCase(StringNullToBlank(CollectionItem(m_colRecord, "PaymentType"))), "3RD")
        objPackage.PaymentType = fsmBill_3rd_Party
        objPackage.Recipient.AccountNo = StringNullToBlank(CollectionItem(m_colRecord, "OriginAccount"))
        objIntl.DutyTaxPayType = fsmBill_3rd_Party
        objIntl.DutyTaxAcctNo = StringNullToBlank(CollectionItem(m_colRecord, "OriginAccount"))
    Case Cafe1.Screen.GetSender.AccountNo <> StringNullToBlank(CollectionItem(m_colRecord, "OriginAccount"))
        objPackage.PaymentType = fsmBill_3rd_Party
        objPackage.Recipient.AccountNo = StringNullToBlank(CollectionItem(m_colRecord, "OriginAccount"))
        objIntl.DutyTaxPayType = fsmBill_3rd_Party
        objIntl.DutyTaxAcctNo = StringNullToBlank(CollectionItem(m_colRecord, "OriginAccount"))
    Case Else
        objPackage.PaymentType = fsmBill_Sender
        objIntl.DutyTaxPayType = fsmBill_Sender
    End Select
    
    If StringNullToBlank(CollectionItem(m_colRecord, "Weight")) <> "" Then
        If TypeName(objPackage) = "MPS" Then
            i = 0
            For Each objPackage1 In objPackage.Packages
                ' Logfile "ShipNext, upd package " & i & ", weight " & CDbl(Me.ShipWeight(i))
                objPackage1.Weight = CDbl(Me.ShipWeight(i))
                i = i + 1
            Next objPackage1
        Else
            ' Logfile "ShipNext, upd package weight " & Me.ShipWeight(0)
            objPackage.Weight = Me.ShipWeight(0)
        End If
    End If
    
    If StringNullToBlank(CollectionItem(m_colRecord, "Dims")) <> "" Then
        If TypeName(objPackage) = "MPS" Then
            i = 0
            For Each objPackage1 In objPackage.Packages
                objPackage1.Length = CDbl(Me.ShipDims(i).Item(1))
                objPackage1.Width = CDbl(Me.ShipDims(i).Item(2))
                objPackage1.Height = CDbl(Me.ShipDims(i).Item(3))
                
                ' Logfile "ShipNext, 4, dims length " & objPackage1.Length
                
                i = i + 1
            Next objPackage1
        Else
            objPackage.Dimensions.Length = CDbl(Me.ShipDims(0).Item(1))
            objPackage.Dimensions.Width = CDbl(Me.ShipDims(0).Item(2))
            objPackage.Dimensions.Height = CDbl(Me.ShipDims(0).Item(3))
            
            ' Logfile "ShipNext, 5, dims length " & objPackage.Dimensions.Length
            
        End If
    End If
    
    
    If TypeName(objPackage) = "MPS" Then
        For Each objPackage1 In objPackage.Packages
            ' objPackage1.PurchaseOrder = StringNullToBlank(CollectionItem(m_colRecord, "RecipientCode"))
            objPackage1.Reference = StringNullToBlank(CollectionItem(m_colRecord, "RecipientCode"))
        Next objPackage1
    Else
        ' objPackage.PurchaseOrder = StringNullToBlank(CollectionItem(m_colRecord, "RecipientCode"))
        objPackage.Reference = StringNullToBlank(CollectionItem(m_colRecord, "RecipientCode"))
        objPackage.CustomerReference = StringNullToBlank(CollectionItem(m_colRecord, "RecipientCode"))
    End If
    
    If Trim(CollectionItem(m_colRecord, "RecipientCountry")) <> "CA" _
    And Trim(CollectionItem(m_colRecord, "RecipientCountry")) <> "US" Then
        objPackage.Intl.B13A = fsmNoB13A_Required
    End If
    
    If Trim(CollectionItem(m_colRecord, "RecipientCountry")) = "US" And IncludeTD Then
    
        ' Logfile "ExcelBatch, ShipNext, Intl TD"
        objIntl.IncludeTD = True
        
        ' 2011-11-10, JFW, Moot point, because no more TD, SoldTo may apply to child shipment.
        If StringNullToBlank(CollectionItem(m_colRecord, "OriginName")) <> "" Then
            'logfile "IOR"
            objIntl.CommercialInvoice.Importer.Country = StringNullToBlank(CollectionItem(m_colRecord, "OriginCountry"))
            ' .ExtendedProperties.Add "170", StringNullToBlank(CollectionItem(m_colRecord,"IORContact"))
            objIntl.CommercialInvoice.Importer.Company = StringNullToBlank(CollectionItem(m_colRecord, "OriginName"))
            objIntl.CommercialInvoice.Importer.Address1 = StringNullToBlank(CollectionItem(m_colRecord, "OriginAddress1"))
            objIntl.CommercialInvoice.Importer.Address2 = StringNullToBlank(CollectionItem(m_colRecord, "OriginAddress2"))
            objIntl.CommercialInvoice.Importer.City = StringNullToBlank(CollectionItem(m_colRecord, "OriginCity"))
            objIntl.CommercialInvoice.Importer.State = StringNullToBlank(CollectionItem(m_colRecord, "OriginProvince"))
            objIntl.CommercialInvoice.Importer.Zip = StringNullToBlank(CollectionItem(m_colRecord, "OriginPostalCode"))
            objIntl.CommercialInvoice.Importer.Phone = "4162999847"
            objIntl.CommercialInvoice.Importer.TaxID = StringNullToBlank(CollectionItem(m_colRecord, "OriginTaxID"))
        End If
        
    ElseIf Trim(CollectionItem(m_colRecord, "RecipientCountry")) <> "CA" Then
        objIntl.IncludeCommercialInvoice = True
        objIntl.CurrencyType = "USD"
        objIntl.TermsOfSale = fsmFOB_FCA

        ' 2011-11-10, JFW, IOR not for international use.
        '        If StringNullToBlank(CollectionItem(m_colRecord, "OriginName")) <> "" Then
        '            'logfile "IOR"
        '            objIntl.CommercialInvoice.Importer.Country = StringNullToBlank(CollectionItem(m_colRecord, "OriginCountry"))
        '            ' .ExtendedProperties.Add "170", StringNullToBlank(CollectionItem(m_colRecord,"IORContact"))
        '            objIntl.CommercialInvoice.Importer.Company = StringNullToBlank(CollectionItem(m_colRecord, "OriginName"))
        '            objIntl.CommercialInvoice.Importer.Address1 = StringNullToBlank(CollectionItem(m_colRecord, "OriginAddress1"))
        '            objIntl.CommercialInvoice.Importer.Address2 = StringNullToBlank(CollectionItem(m_colRecord, "OriginAddress2"))
        '            objIntl.CommercialInvoice.Importer.City = StringNullToBlank(CollectionItem(m_colRecord, "OriginCity"))
        '            objIntl.CommercialInvoice.Importer.State = StringNullToBlank(CollectionItem(m_colRecord, "OriginProvince"))
        '            objIntl.CommercialInvoice.Importer.Zip = StringNullToBlank(CollectionItem(m_colRecord, "OriginPostalCode"))
        '            objIntl.CommercialInvoice.Importer.Phone = "4162999847"
        '            objIntl.CommercialInvoice.Importer.TaxID = StringNullToBlank(CollectionItem(m_colRecord, "OriginTaxID"))
        '        End If
    
        With objIntl
            .PackageType = fsmFedEx_Intl_Box
            
            ' Logfile "ExcelBatch, ShipNext, Intl UploadTrade"
            
            .IncludeTD = False
            .UploadTradeDocumentsFlag = True
            .IncludeCommercialInvoice = True
            
            If CafeShipment.MPS.Packages.Count > 1 Then
                With CafeShipment.MPS.International
                    
                    If CafeShipment.MPS.ServiceType = fsmGround _
                    Or CafeShipment.MPS.ServiceType = fsmHome_Delivery Then
                        ' 2011-11-11, JFW, FSM/FXI bug, default IOR is the Recipient; should be the Sender.
                        Set objSender = Cafe1.Screen.GetSender
                        .CommercialInvoice.Importer.Country = objSender.Country
                        .CommercialInvoice.Importer.Company = objSender.Company
                        .CommercialInvoice.Importer.Address1 = objSender.Address1
                        .CommercialInvoice.Importer.Address2 = objSender.Address2
                        .CommercialInvoice.Importer.City = objSender.City
                        .CommercialInvoice.Importer.State = objSender.State
                        .CommercialInvoice.Importer.Zip = objSender.Zip
                        .CommercialInvoice.Importer.Phone = objSender.Phone
                        .CommercialInvoice.Importer.TaxID = objSender.TaxID
                    End If
                    
                    If .Commodities.Count <= 0 Then
                        With .CommercialInvoice
                            .InvoiceType = fsmCommercialInvoice
                            .Copies = 0
                            .InvoicePurpose = fsmSold
                        End With
                    Else
                        With .CommercialInvoice
                            .InvoiceType = fsmMyOwnCommercialInvoice
                            .Copies = 0
                            .InvoicePurpose = fsmSold
                            .InvoicePath = Cafe4.CreateShipmentCI(CafeShipment)
                        End With
                    End If
                End With
            Else
                With CafeShipment.Package.Intl
                    
                    If CafeShipment.Package.ServiceType = fsmGround _
                    Or CafeShipment.Package.ServiceType = fsmHome_Delivery Then
                        ' 2011-11-11, JFW, FSM/FXI bug, default IOR is the Recipient; should be the Sender.
                        Set objSender = Cafe1.Screen.GetSender
                        .CommercialInvoice.Importer.Country = objSender.Country
                        .CommercialInvoice.Importer.Company = objSender.Company
                        .CommercialInvoice.Importer.Address1 = objSender.Address1
                        .CommercialInvoice.Importer.Address2 = objSender.Address2
                        .CommercialInvoice.Importer.City = objSender.City
                        .CommercialInvoice.Importer.State = objSender.State
                        .CommercialInvoice.Importer.Zip = objSender.Zip
                        .CommercialInvoice.Importer.Phone = objSender.Phone
                        .CommercialInvoice.Importer.TaxID = objSender.TaxID
                    End If
                    
                    If .Commodities.Count <= 0 Then
                        With .CommercialInvoice
                            .InvoiceType = fsmCommercialInvoice
                            .Copies = 0
                            .InvoicePurpose = fsmSold
                        End With
                    ElseIf False Then
                        With .CommercialInvoice
                            .InvoiceType = fsmCommercialInvoice
                            .Copies = 1
                            .InvoicePurpose = fsmSold
                        End With
                    Else
                        With .CommercialInvoice
                            .InvoiceType = fsmMyOwnCommercialInvoice
                            .Copies = 0
                            .InvoicePurpose = fsmSold
                            .InvoicePath = Cafe4.CreatePackageCI(CafeShipment.Package)
                        End With
                    End If
                End With
            End If
    
        End With
    
    End If
    
    If False And Windows.ComputerName = "CAN-00325000" Then
            With objIntl.Commodities
                .Clear
                With .Add("1")
                    .CountryOfMfg = "CA"
                    .Description = "Test item"
                    .UnitOfMeasure = "EA"
                    .UnitQuantity = 1
                    .UnitValue = 10
                End With
            End With
    End If
    
    ShipNext = True
    Exit Function
    
ErrorExit:
    Err.Raise 12345, "ExcelBatch.ShipNext", Err.Description
    Resume
    
End Function


Public Function SoldTo(Optional objShipment As Shipment = Nothing) As String

    Dim strIOR As String
    Dim objSender As Sender
    
    If Not objShipment Is Nothing Then
        If objShipment.MPS.Packages.Count > 0 Then
            ' 2011-11-10, JFW, MPS is not TD and SoldTo has no bearing.
            '            objShipment.MPS.International.CommercialInvoice.Importer.Country = StringNullToBlank(CollectionItem(m_colRecord, "OriginCountry"))
            '            objShipment.MPS.International.CommercialInvoice.Importer.Company = StringNullToBlank(CollectionItem(m_colRecord, "OriginName"))
            '            objShipment.MPS.International.CommercialInvoice.Importer.Address1 = StringNullToBlank(CollectionItem(m_colRecord, "OriginAddress1"))
            '            objShipment.MPS.International.CommercialInvoice.Importer.Address2 = StringNullToBlank(CollectionItem(m_colRecord, "OriginAddress2"))
            '            objShipment.MPS.International.CommercialInvoice.Importer.City = StringNullToBlank(CollectionItem(m_colRecord, "OriginCity"))
            '            objShipment.MPS.International.CommercialInvoice.Importer.State = StringNullToBlank(CollectionItem(m_colRecord, "OriginProvince"))
            '            objShipment.MPS.International.CommercialInvoice.Importer.Zip = StringNullToBlank(CollectionItem(m_colRecord, "OriginPostalCode"))
            '            objShipment.MPS.International.CommercialInvoice.Importer.Phone = "4162999847"
            '            objShipment.MPS.International.CommercialInvoice.Importer.TaxID = StringNullToBlank(CollectionItem(m_colRecord, "OriginTaxID"))
        
            ' 2011-11-11, JFW, FSM/FXI bug, default IOR is the Recipient; should be the Sender.
            If objShipment.MPS.ServiceType = fsmGround _
            Or objShipment.MPS.ServiceType = fsmHome_Delivery Then
                Set objSender = Cafe1.Screen.GetSender
                objShipment.MPS.International.CommercialInvoice.Importer.Country = objSender.Country
                objShipment.MPS.International.CommercialInvoice.Importer.Company = objSender.Company
                objShipment.MPS.International.CommercialInvoice.Importer.Address1 = objSender.Address1
                objShipment.MPS.International.CommercialInvoice.Importer.Address2 = objSender.Address2
                objShipment.MPS.International.CommercialInvoice.Importer.City = objSender.City
                objShipment.MPS.International.CommercialInvoice.Importer.State = objSender.State
                objShipment.MPS.International.CommercialInvoice.Importer.Zip = objSender.Zip
                objShipment.MPS.International.CommercialInvoice.Importer.Phone = objSender.Phone
                objShipment.MPS.International.CommercialInvoice.Importer.TaxID = objSender.TaxID
            End If
            
        Else
            ' 2011-11-10, JFW, IOR as SoldTo is good for TD and will have an effect if TD master CI is on
            If objShipment.Package.Intl.IncludeTD Then
                objShipment.Package.Intl.CommercialInvoice.Importer.Country = StringNullToBlank(CollectionItem(m_colRecord, "OriginCountry"))
                objShipment.Package.Intl.CommercialInvoice.Importer.Company = StringNullToBlank(CollectionItem(m_colRecord, "OriginName"))
                objShipment.Package.Intl.CommercialInvoice.Importer.Address1 = StringNullToBlank(CollectionItem(m_colRecord, "OriginAddress1"))
                objShipment.Package.Intl.CommercialInvoice.Importer.Address2 = StringNullToBlank(CollectionItem(m_colRecord, "OriginAddress2"))
                objShipment.Package.Intl.CommercialInvoice.Importer.City = StringNullToBlank(CollectionItem(m_colRecord, "OriginCity"))
                objShipment.Package.Intl.CommercialInvoice.Importer.State = StringNullToBlank(CollectionItem(m_colRecord, "OriginProvince"))
                objShipment.Package.Intl.CommercialInvoice.Importer.Zip = StringNullToBlank(CollectionItem(m_colRecord, "OriginPostalCode"))
                objShipment.Package.Intl.CommercialInvoice.Importer.Phone = "4162999847"
                objShipment.Package.Intl.CommercialInvoice.Importer.TaxID = StringNullToBlank(CollectionItem(m_colRecord, "OriginTaxID"))
            Else
                ' 2011-11-11, JFW, FSM/FXI bug, default IOR is the Recipient; should be the Sender.
                If objShipment.Package.ServiceType = fsmGround _
                Or objShipment.Package.ServiceType = fsmHome_Delivery Then
                    Set objSender = Cafe1.Screen.GetSender
                    objShipment.Package.Intl.CommercialInvoice.Importer.Country = objSender.Country
                    objShipment.Package.Intl.CommercialInvoice.Importer.Company = objSender.Company
                    objShipment.Package.Intl.CommercialInvoice.Importer.Address1 = objSender.Address1
                    objShipment.Package.Intl.CommercialInvoice.Importer.Address2 = objSender.Address2
                    objShipment.Package.Intl.CommercialInvoice.Importer.City = objSender.City
                    objShipment.Package.Intl.CommercialInvoice.Importer.State = objSender.State
                    objShipment.Package.Intl.CommercialInvoice.Importer.Zip = objSender.Zip
                    objShipment.Package.Intl.CommercialInvoice.Importer.Phone = objSender.Phone
                    objShipment.Package.Intl.CommercialInvoice.Importer.TaxID = objSender.TaxID
                End If
            End If
        End If
        
    ElseIf StringNullToBlank(CollectionItem(m_colRecord, "OriginName")) <> "" Then
        strIOR = strIOR & "169,""" & StringNullToBlank(CollectionItem(m_colRecord, "OriginCountry")) & """"
        strIOR = strIOR & "171,""" & StringNullToBlank(CollectionItem(m_colRecord, "OriginName")) & """"
        strIOR = strIOR & "172,""" & StringNullToBlank(CollectionItem(m_colRecord, "OriginAddress1")) & """"
        strIOR = strIOR & "173,""" & StringNullToBlank(CollectionItem(m_colRecord, "OriginAddress2")) & """"
        strIOR = strIOR & "174,""" & StringNullToBlank(CollectionItem(m_colRecord, "OriginCity")) & """"
        strIOR = strIOR & "175,""" & StringNullToBlank(CollectionItem(m_colRecord, "OriginProvince")) & """"
        strIOR = strIOR & "176,""" & StringNullToBlank(CollectionItem(m_colRecord, "OriginPostalCode")) & """"
        strIOR = strIOR & "178,""" & "4162999847" & """"
        strIOR = strIOR & "180,""" & StringNullToBlank(CollectionItem(m_colRecord, "OriginTaxID")) & """"
    Else
        strIOR = strIOR & "169,""" & Trim(CollectionItem(m_colRecord, "RecipientCountry")) & """"
        strIOR = strIOR & "171,""" & Trim(StringNullToBlank(CollectionItem(m_colRecord, "RecipientCompany"))) & """"
        strIOR = strIOR & "172,""" & Trim(StringNullToBlank(CollectionItem(m_colRecord, "RecipientAddress1"))) & """"
        strIOR = strIOR & "173,""" & Trim(StringNullToBlank(CollectionItem(m_colRecord, "RecipientAddress2"))) & """"
        strIOR = strIOR & "174,""" & Trim(CollectionItem(m_colRecord, "RecipientCity")) & """"
        strIOR = strIOR & "175,""" & Trim(CollectionItem(m_colRecord, "RecipientProvince")) & """"
        If Trim(CollectionItem(m_colRecord, "RecipientCountry")) = "US" Then
            If Len(Trim(CollectionItem(m_colRecord, "RecipientPostalCode"))) <= 5 Then
                strIOR = strIOR & "176,""" & Right("00000" & Trim(CollectionItem(m_colRecord, "RecipientPostalCode")), 5) & """"
            Else
                strIOR = strIOR & "176,""" & Right("000000000" & Trim(CollectionItem(m_colRecord, "RecipientPostalCode")), 9) & """"
            End If
        Else
            strIOR = strIOR & "176,""" & CollectionItem(m_colRecord, "RecipientPostalCode") & """"
        End If
        strIOR = strIOR & "178,""" & "4162999847" & """"
        ' strIOR = strIOR & "180,""" & StringNullToBlank(CollectionItem(m_colRecord, "OriginTaxID")) & """"
    End If
    
    SoldTo = strIOR
    
End Function

' Called for TD MPS
Public Function ShipDims(ByVal lngPackage As Long) As Collection
    Dim i As Long
    Dim strDims() As String
    Dim strDim() As String
    
    If StringNullToBlank(CollectionItem(m_colRecord, "Dims")) <> "" Then
        strDims = Split(StringNullToBlank(CollectionItem(m_colRecord, "Dims")), ",")
        If lngPackage > UBound(strDims) Then
            lngPackage = UBound(strDims)
        End If
        strDim = Split(LCase(strDims(lngPackage)), "x")
        Set ShipDims = New Collection
        For i = LBound(strDim) To UBound(strDim)
            If Not IsNumeric(strDim(i)) Then
                MsgBox "Invalid dims (" & strDims(i) & "), line " _
                        & Trim(StringNullToBlank(CollectionItem(m_colRecord, "RecipientCode")))
                Set ShipDims = Nothing
                Exit Function
            End If
            ShipDims.Add strDim(i)
        Next i
    End If
    
End Function

Public Function ShipWeight(ByVal lngPackage As Long) As String

    Dim i As Long
    Dim strWeight() As String
    
    If StringNullToBlank(CollectionItem(m_colRecord, "Weight")) <> "" Then
        strWeight = Split(StringNullToBlank(CollectionItem(m_colRecord, "Weight")), ",")
        For i = LBound(strWeight) To UBound(strWeight)
            If Not IsNumeric(strWeight(i)) Then
                MsgBox "Invalid weight (" & strWeight(i) & "), line " _
                        & Trim(StringNullToBlank(CollectionItem(m_colRecord, "RecipientCode")))
                Exit Function
            End If
        Next i
    
        If lngPackage > UBound(strWeight) Then
            lngPackage = UBound(strWeight)
        End If
        ShipWeight = strWeight(lngPackage)
    End If
    
End Function

Private Sub Class_Terminate()

    On Error GoTo ErrorExit
    
    If Not m_objWorkbook Is Nothing Then
        m_objWorkbook.Close True
        Set m_objWorkbook = Nothing
    End If
    
    If Not m_objExcel Is Nothing Then
        m_objExcel.Quit
        Set m_objExcel = Nothing
    End If
    
    Exit Sub
    
ErrorExit:
    Err.Raise 12345, "ExcelBatch.Terminate", _
        "Error closing Excel.  Examine Windows process and kill Excel.  " _
            & Err.Description

End Sub
